package com.lgcns.weather.db;

import java.util.ArrayList;
import java.util.HashMap;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.lgcns.weather.info.LocationInfo;

public class QueryHelper {

    private QueryHelper() {
    }

    /**
     * 관심지역의 기상 정보 코드를 조회한다.
     * 
     * @param context
     * @param locationId
     * @return
     */
    public static LocationInfo getLocationInfo(Context context, int locationId) {
        WeatherDBHelper dbHelper = new WeatherDBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db
                .rawQuery(
                        "select _id, loc1, loc2, loc3, now_stn_id, gridx, gridy, week_temp, week_code, week_explain from address where _id = "
                                + locationId, null);

        cursor.moveToNext();

        LocationInfo locationInfo = new LocationInfo();
        locationInfo.setLocationId(cursor.getInt(cursor.getColumnIndex("_id")));
        locationInfo.setLoc1(cursor.getString(cursor.getColumnIndex("loc1")));
        locationInfo.setLoc2(cursor.getString(cursor.getColumnIndex("loc2")));
        locationInfo.setLoc3(cursor.getString(cursor.getColumnIndex("loc3")));
        locationInfo.setNowStnId(cursor.getString(cursor.getColumnIndex("now_stn_id")));
        locationInfo.setGridX(cursor.getString(cursor.getColumnIndex("gridx")));
        locationInfo.setGridY(cursor.getString(cursor.getColumnIndex("gridy")));
        locationInfo.setWeekTemp(cursor.getString(cursor.getColumnIndex("week_temp")));
        locationInfo.setWeekCode(cursor.getString(cursor.getColumnIndex("week_code")));
        locationInfo.setWeekExplain(cursor.getString(cursor.getColumnIndex("week_explain")));

        cursor.close();
        db.close();
        dbHelper.close();

        return locationInfo;
    }

    /**
     * 지역(도/특별시) 목록을 조회한다.
     * 
     * @param context
     * @return
     */
    public static ArrayList<String> getLoc1List(Context context) {
        WeatherDBHelper dbHelper = new WeatherDBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select loc1 from address group by loc1 order by loc1", null);

        ArrayList<String> list = new ArrayList<String>();
        while (cursor.moveToNext()) {
            list.add(cursor.getString(cursor.getColumnIndex("loc1")));
        }

        cursor.close();
        db.close();
        dbHelper.close();

        return list;
    }

    /**
     * 지역(시/군) 목록을 조회한다.
     * 
     * @param context
     * @param loc1
     * @return
     */
    public static ArrayList<String> getLoc2List(Context context, String loc1) {
        WeatherDBHelper dbHelper = new WeatherDBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select loc2 from address where loc1='" + loc1
                + "' group by loc2 order by loc2", null);

        ArrayList<String> list = new ArrayList<String>();
        while (cursor.moveToNext()) {
            list.add(cursor.getString(cursor.getColumnIndex("loc2")));
        }

        cursor.close();
        db.close();
        dbHelper.close();

        return list;
    }

    /**
     * 지역(동/면) 목록을 조회한다.
     * 
     * @param context
     * @param loc1
     * @param loc2
     * @return
     */
    public static HashMap<String, ArrayList> getLoc3List(Context context, String loc1, String loc2) {
        WeatherDBHelper dbHelper = new WeatherDBHelper(context);
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery("select _id, loc3 from address where loc1='" + loc1 + "' and loc2='"
                + loc2 + "' order by loc3", null);

        ArrayList<Integer> idList = new ArrayList<Integer>();
        ArrayList<String> loc3List = new ArrayList<String>();

        while (cursor.moveToNext()) {
            idList.add(cursor.getInt(cursor.getColumnIndex("_id")));
            loc3List.add(cursor.getString(cursor.getColumnIndex("loc3")));
        }

        HashMap<String, ArrayList> map = new HashMap<String, ArrayList>();
        map.put("id", idList);
        map.put("loc3", loc3List);

        cursor.close();
        db.close();
        dbHelper.close();

        return map;
    }

}
